<?php
    header('Content-type: application/json');
    error_reporting(E_ALL);
    
    mysql_connect("localhost", "Lgf","4tt~5Uf1");
    mysql_select_db("Lgf");
    $jsonObj= array();
    
    /**
     * This function tests the sent user credentials and, if the user/password combination is correct, returns the user's data instance. If wrong an errorcode is returned.
     * @param string $mail the user's mailadress as the login name
     * @param string $pass the sha256 hash of the users password
     * @return multitype:|multitype:string
     */
    function doLogin($mail, $pass){
        $query="SELECT * FROM `User` WHERE `mail` LIKE '$mail' AND `pass` LIKE '$pass'";
        $res=mysql_query($query);
        if(mysql_num_rows($res)>0){
            return mysql_fetch_assoc($res);
        }
        else{
            return array('errorcode'=>"unknownUserPassCombination");
        }
        return array('errorcode'=>"unknownError");
    }
    
    /**
     * This function is used to create a new user using the combination of a unique mail adress, a name and a password. If the mail is already in ued an errorcode is returned, otherwise the errorcode success is returned.
     * @param string $mail
     * @param string $name
     * @param string $pass
     * @return multitype:string
     */
    function register($mail, $name, $pass){
        $query="SELECT * FROM `User` WHERE `mail` LIKE '$mail';";
        $res=mysql_query($query);
        if (mysql_num_rows($res)>0){
            return array('errorcode'=>"dublicate");
        }
        $query="INSERT INTO `Lgf`.`User` (`id`, `mail`, `name`, `pass`, `bild`) VALUES (NULL, '$mail', '$name', '$pass', '');";
        if (mysql_query($query)==1){
            return array('errorcode'=>"success");
        }
        return array('errorcode'=>"unknownError");;
        
    }
    
    /**
     * This function resets the users password, creates a new one and mails it to the user. If everything worked out the errorcode success is returned, in case of a mysql-error an unknown error is returned.
     * @param string $mail the user's mail.
     * @return multitype:string
     */
    function resetPW($mail){
        $zeichen = '0123456789';
        $zeichen .= 'abcdefghijklmnopqrstuvwxyz';
        $zeichen .= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $zeichen .= '()!.:=';
        $str = '';
        $anz = strlen($zeichen);
        for ($i=0; $i<8; $i++) {
            $str .= $zeichen[rand(0,$anz-1)];
        }
        $pass=hash('sha256', $str);
        $query="SELECT * FROM `User` WHERE `mail` LIKE '$mail'";
        if (mysql_num_rows(mysql_query($query))==0){
            return array("errorcode"=>"noSuchUser");
        }
        $query = "UPDATE `Lgf`.`User` SET `pass` = '$pass' WHERE `User`.`mail` LIKE '$mail';";
        if (mysql_query($query)==1){
            $betreff = "Neues Passwort";
            $from = "From: Support <support@lgf.aw.gl>";
            $text = "Hi, anbei dein neues Passwort \n $str";
            mail($mail, $betreff, $text, $from);
            return array("errorcode"=>"success");
        }
        else{
            return array("errorcode"=>"unknownError");
        }
        
        
    }
    
    /**
     * This function returns the user instance matching to the submitted id
     * @param unknown $id
     * @return multitype:string
     */
    function getUserByID($id){
        $query="SELECT * FROM `User` WHERE `id` = $id";
        return mysql_fetch_assoc(mysql_query($query));
    }
    
    /**
     * This function retuns the event matching the submitted id
     * @param int $id
     * @return multitype:string |multitype:string
     */
    function getVeranstaltungByID($id){
        $query="SELECT * FROM `Veranstaltungen` WHERE `id` = $id;";
        $res=mysql_query($query);
        if (mysql_num_rows($res)==0){
            return array('errorcode'=>"doesNotExist");
        }
        
        return mysql_fetch_assoc($res);
    }
    
    /**
     * This function is used to return all events close to the searching user's coordinate within the submitted radius.
     * @param double $gpsX
     * @param double $gpsY
     * @param double $dist
     * @return multitype:string
     */
    function getVeranstaltungenUmkreis($gpsX, $gpsY, $dist){
        $query="SELECT *,(((acos(sin(($gpsX*pi()/180)) * sin((`gpsX`*pi()/180))+cos(($gpsX*pi()/180)) * cos((`gpsX`*pi()/180)) * cos((($gpsY-`gpsY`)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM `Veranstaltungen` WHERE (((acos(sin(($gpsX*pi()/180)) *sin((`gpsX`*pi()/180))+cos(($gpsX*pi()/180)) * cos((`gpsX`*pi()/180)) * cos(((gpsY-`gpsY`)*pi()/180))))*180/pi())*60*1.1515*1.609344) <10 AND `ablauf` > LOCALTIMESTAMP";
        $res=mysql_query($query);
        while($r = mysql_fetch_assoc($res)) {
            $result[] = $r;
        }
        return $result;
    }
    
    /**
     * This function is used to return all available fields of study.
     * @return multitype:multitype:string
     */
    function getStudyFields(){
        $query="SELECT * FROM `Fachgebiet`";
        $result = array();
        $res=mysql_query($query);
        while($r = mysql_fetch_assoc($res)) {//ggf mysql_fetch_array
            $result[] = $r;
        }
        return $result;
    }
    
    /**
     * This function is used to retun a field of study matching the submitted id.
     * @param int $id
     * @return multitype:multitype:string
     */
    function getStudyFieldsID($id){
        $query="SELECT * FROM `Fachgebiet` WHERE `id`=$id";
        $result = array();
        $res=mysql_query($query);
        while($r = mysql_fetch_assoc($res)) {
            $result[] = $r;
        }
        return $result;
    }
    
    /**
     * This function is used to set the users intrests.
     *
     * For each field of study id the function tests if there's already a similar entry.
     * For the input of 0 an errorcode is returned.
     * In case of an mysql error the unknownError errorcode is returned.
     * @param int $user
     * @param string $intrests comma seperated array of the ids of the selected fields of study.
     * @return multitype:string |multitype:multitype:string
     */
    function setUserIntrests($user,$intrests){
        if ($user==0){
            return array('errorcode'=>"wrongUserInput");
        }
        if ($intrests==0){
            mysql_query("DELETE FROM `Lgf`.`UserInteressen` WHERE `UserInteressen`.`UserID` = 1;");
            $result=array();
            $query="SELECT * FROM `UserInteressen` WHERE `UserID`=$user";
            $res=mysql_query($query);
            while($r = mysql_fetch_assoc($res)) {
                $result[] = $r;
            }
            return $result;
        }
        $intrests= explode(',', $intrests);
        
        mysql_query("DELETE FROM `Lgf`.`UserInteressen` WHERE `UserInteressen`.`UserID` = 1;");
		
        foreach ($intrests as $interesse){
            $res=mysql_query("INSERT INTO `Lgf`.`UserInteressen` (`UserID`, `FachgebietID`) VALUES ('$user', '$interesse');");
            if ($res==0){
                return  array('errorcode'=>"unknownError");;
            }
        }
        $result=array();
        $query="SELECT * FROM `UserInteressen` WHERE `UserID`=$user";
        $res=mysql_query($query);
        while($r = mysql_fetch_assoc($res)) {
            $result[] = $r;
        }
        return $result;
    }
    
    /**
     * This function returns all selected fields of study's ids selected by the submitted user
     * @param int $user
     * @return multitype:multitype:string
     */
    function getUserIntrests($user){
        $query="SELECT * FROM `UserInteressen` WHERE `UserID`=$user";
        $result = array();
        $res=mysql_query($query);
        while($r = mysql_fetch_assoc($res)) {
            $result[] = $r;
        }
        return $result;
    }
    
    /**
     * This function is used to return the events instances a user subscribed to.
     * @param int $userID
     * @return multitype:multitype:string|int
     */
    function getAbos($userID){
        $query="SELECT * FROM `Abos` A, Veranstaltungen V WHERE A.UserID=$userID AND A.VeranstaltungID = V.id";
        $result = array();
        $res=mysql_query($query);
        while($r = mysql_fetch_assoc($res)) {
            $result[] = $r;
        }
        return $result;
    }
    
    /**
     * This function is used to subscribe a user to an event and to retun the subscribed event in case of success.
     *
     * If this subscription already exists nothing happens, if a mysql error happens during runtime the unknownError is returned.
     * @param int $userID
     * @param int $veranstaltung
     * @return multitype:string |multitype:multitype:string
     */
    function doAbo($userID,$veranstaltung){
        if(mysql_num_rows(mysql_query("SELECT *  FROM `Abos` WHERE `UserID` = $userID AND `VeranstaltungID` = $veranstaltung"))>0){
			return array("errorcode"=>"dublicateAbo");
        }
        else {
            $res=mysql_query($query="INSERT INTO `Lgf`.`Abos` (`UserID`, `VeranstaltungID`) VALUES ('$userID', '$veranstaltung');");
            if ($res==0){
                return  array('errorcode'=>"unknownError");;
            }
        }
        $result=array();
        $query="SELECT *  FROM `Abos` WHERE `UserID` = $userID AND `VeranstaltungID` = $veranstaltung";
        return array("errorcode"=>"success");
    }
    
    /**
     * This function is used to create a new event and in case of success to retun the instance of the newly created event, otherwise the unknownError.
     * @param string $name
     * @param string $beschreibung
     * @param int $fachgebiet
     * @param int $building
     * @param string $date
     * @param string $expires
     * @param double $gpsX
     * @param double $gpsY
     * @return multitype:string|multitype:string
     */
    function createEvent($name, $beschreibung, $fachgebiet, $building, $date, $expires, $gpsX, $gpsY){
        $query="INSERT INTO `Lgf`.`Veranstaltungen` (`id`, `name`, `beschreibung`, `fachgebiet`, `gebaeude`, `datum`, `ablauf`, `gpsX`, `gpsY`) VALUES (NULL, '$name', '$beschreibung', '$fachgebiet', '$building', '$date', '$expires', '$gpsX', '$gpsY');";
        if(mysql_query($query)==1){
            $query="SELECT * FROM `Veranstaltungen` ORDER BY `id`DESC";
            return mysql_fetch_assoc(mysql_query($query));
        }
        else{
            return array("errorcode"=>"unknownError");
        }
    }
    
    /**
     * This function is used to get all buildings close to the user's serach position.
     * @param double $gpsX
     * @param double $gpsY
     * @param double $dist
     * @return multitype:array
     */
    function getBuildingsClose($gpsX,$gpsY,$dist){
        $query="SELECT *,(((acos(sin(($gpsX*pi()/180)) * sin((`gpsX`*pi()/180))+cos(($gpsX*pi()/180)) * cos((`gpsX`*pi()/180)) * cos((($gpsY-`gpsY`)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM `Gebaeude` WHERE (((acos(sin(($gpsX*pi()/180)) *sin((`gpsX`*pi()/180))+cos(($gpsX*pi()/180)) * cos((`gpsX`*pi()/180)) * cos(((gpsY-`gpsY`)*pi()/180))))*180/pi())*60*1.1515*1.609344) <10";
        $res=mysql_query($query);
        if (mysql_num_rows($res)){
            return array("errorcode"=>"noEntriesFound");
        }
        while($r = mysql_fetch_assoc($res)) {
            $result[] = $r;
        }
        return $result;
    }
    
    /**
     * This function is used to change the users password. For security reasons the old password is requested to verify the user.
     *
     * Possible errorCodes
     * success
     * unknownError: Unknown MySQL error
     * wrongUserPassCombination: The submitted user credentials don't match the database's
     * @param int $userID
     * @param string $oldPW
     * @param string $newPW
     * @return multitype:string
     */
    function changePw($userID,$oldPW,$newPW){
        if(mysql_num_rows(mysql_query("Select * FROM `User` WHERE `id`=$userID AND `pass`='$oldPW'"))>0){
            if (mysql_query("UPDATE `Lgf`.`User` SET `pass` = '$newPW' WHERE `User`.`id` = $userID;")==1) {
                return  array('errorcode'=>"success");
            }
            return  array('errorcode'=>"unknownError");
        }
        return  array('errorcode'=>"wrongUserPassCombination");
    }
    
    /**
     * This function is used to change the user's nickname
     *
     * possible errorCodes
     * success:
     * unknownError: MySQL-Error
     * userNotExisting: There's no user matching the submitted userID
     * @param int $userID
     * @param string $newName
     * @return multitype:string
     */
    function changeName($userID,$newName){
        if(mysql_num_rows(mysql_query("Select * FROM `User` WHERE `id`=$userID"))>0){
            if (mysql_query("UPDATE `Lgf`.`User` SET `name` = '$newName' WHERE `User`.`id` = $userID;")==1) {
                return  array('errorcode'=>"success");
            }
            return  array('errorcode'=>"unknownError");
        }
        return  array('errorcode'=>"userNotExisting");
    }
    
    /**
     * This function is used to upload/update the user's profile picture
     * @param int $userID
     * @param binary $file
     */
    function uploadProfilePicture($userID,$file){
        //Please test the filesize in client
        //todo: - Filesizecheck
        //- update userDB-Entry
        if($file==null){
            return array("errorcode"=>"no file submitted");
        }
        
        $uploaddir="/var/www/vhosts/lgf.aw.gl/httpdocs/profilePictures/";
        $getrennt = explode ('.',basename($file['file']['name']));
        $getrennt = array_reverse ($getrennt);
        $endung = $getrennt[0];
        
        $uploadfile=$uploaddir.$userID.".".$endung;
        if(move_uploaded_file($file['file']['tmp_name'], $uploadfile)){
            if (mysql_query("UPDATE `Lgf`.`User` SET `bild` = 'http://lgf.aw.gl/profilePictures/$userID.$endung' WHERE `User`.`id` = $userID;")==1) {
                return array("errorcode"=>"success","bild"=>"http://lgf.aw.gl/profilePictures/$userID.$endung");
            }
            else {
                return array("errorcode"=>"unknownError");
            }
        }
        return array("errorcode"=>"uploadError");
    }
    
    
    /**
     * This function is designed give the client the downloadURLs of all files belongig to an event
     * @param unknown $eventID
     */
    function getEventFiles($eventID){
        $dir="/var/www/vhosts/lgf.aw.gl/httpdocs/events/".$eventID;
        $dirContent=scandir($dir);
        $result=array();
        foreach ($dirContent as $file){
            $result[]="http://lgf.aw.gl/events/$eventID/".$file;
        }
        unset($result[0]);
        unset($result[1]);
        return $result;
    }
    
    /**
     * This function is designed to upload a file to an event
     
     * @param int $eventID
     * @param binary $file
     * @return multitype:string
     */
    function uploadFileToEvent($eventID,$file){
        if($file==null){
            return array("errorcode"=>"no file submitted");
        }
        
        $uploaddir="/var/www/vhosts/lgf.aw.gl/httpdocs/events/".$eventID."/";
        
        mkdir ( $uploaddir, 0777 );
        
        $uploadfile=$uploaddir.time()."_".$file['file']['name'];
        if(move_uploaded_file($file['file']['tmp_name'], $uploadfile)){
            return getEventFiles($eventID);
        }
        
        return array("errorcode"=>"uploadError");
    }
    
    /**
     * This function is designed to checkin as a participant when the user is at the event
     * @param int $userID
     * @param int $eventID
     */
    function checkinToEvent($userID,$eventID){
        if (mysql_num_rows(mysql_query("SELECT * FROM `Checkins` WHERE `userID` = $userID AND `eventID` = $eventID"))==0) {
            if(mysql_query("INSERT INTO `Lgf`.`Checkins` (`id`, `userID`, `eventID`) VALUES (NULL, '$userID', '$eventID');")==1){
                return array("errorcode"=>"success");
            }
            return array("errorcode"=>"unknownSQLError");
        }
        return array("errorcode"=>"alreadyCheckedIn");
    }
    
    
    /**
     * This function is designed to checkin as a participant when the user is at the event
     * @param unknown $eventID
     */
    function getCheckedInUsers($eventID){
        $res=mysql_query("SELECT * FROM `Checkins` c, `User` u WHERE c.eventID=$eventID AND c.userID=u.id");
        $result=array();
        while ($row=mysql_fetch_assoc($res)){
            $result[]=$row;
        }
        if ($result==null) {
            return array("errorcode"=>"noUsersCheckedIn");
        }
        return $result;
    }
    
    
    
    function getEventComments($eventID){
        $query="SELECT c.*, u.name FROM `Comments` c,`User` u WHERE c.eventID=$eventID AND c.userID=u.id ORDER BY c.id DESC";
        $res=mysql_query($query);
        $result=array();
        while($r = mysql_fetch_assoc($res)) {
            $result[] = $r;
        }
        return $result;
    }
    
    /**
     * Creates a comment that is able to contain a poll/vote
     * @param unknown $eventID
     * @param unknown $pollID
     */
    function doComment($userID,$eventID,$comment,$pollID){
        $query="INSERT INTO `Lgf`.`Comments` (`id`, `userID`, `context`, `pollID`, `eventID`) VALUES (NULL, '$userID', '$comment', '$pollID', '$eventID');";
        if (mysql_query($query)==1){
            return getEventComments($eventID);
        }
        return  array("errorcode"=>"unknownError");
    }
    
    /**
     * Gets all comments of an event
     * @param unknown $eventID
     */
    function getComments($eventID){
    	$query="SELECT * FROM `Comments` WHERE `eventID` = 1 ORDER BY id DESC";
    	$res=mysql_query($query);
        $result=array();
        while($r = mysql_fetch_assoc($res)) {
            $result[] = $r;
        }
        return $result;
    }
    
    /**
     * Function to send an eMail to a specific user
     * @param string $userID
     * @param string $recipientID
     * @param string $content
     * @return multitype:string
     */
    function contactUser($userID,$recipientID,$content){
        $user=getUserByID($userID);
        $username=$user['name'];
        $userMail=$user['mail'];
        $mailtext="Hallo, \n du hast soeben durch $username eine Nachricht ueber den Dienst Lerngruppenfinder erhalten. \n =========== \n";
        $mailtext.=$content;
        $recipient=getUserByID($recipientID);
        $recipientMail=$recipient['mail'];
        
        $subject="Neue Nachricht von $username";
        
        $header = "From: $userMail" . "\r\n" .
        "Reply-To: $userMail" . "\r\n" .
        'X-Mailer: PHP/' . phpversion();
        mail($recipientMail, $subject, $mailtext, $header);
        
        return array("errorcode"=>"success");
    }
    
    /**
     * Function to retun a poll including the possible answers and the actual count of the votes
     * @param int $pollID
     * @return multitype:string |multitype:string
     */
    function getPoll($pollID){
        $query="SELECT * FROM (Polls p LEFT JOIN (Answers a) ON (a.pollID=p.id)) WHERE p.id=$pollID";
        $res=mysql_query($query);
        $result=array();
        if (mysql_num_rows($res)==0) {
            return array("errorcode"=>"noSuchPoll");
        }
        
        
        while($r = mysql_fetch_assoc($res)) {
            $result[0][] = $r;
        }
        
        $query="SELECT `pollID`,`AnswerID`,COUNT(*) as count FROM `Votes` WHERE `pollID`=$pollID GROUP BY AnswerID";
        $res=mysql_query($query);
        while($r = mysql_fetch_assoc($res)) {
            $result[1][] = $r;
        }
        return $result;
    }
    
    function createPoll($question,$options){
        $question= mysql_real_escape_string($question);
        mysql_query("INSERT INTO `Lgf`.`Polls` (`id`, `question`) VALUES (NULL, '$question');");
        $res=mysql_query(" SELECT  LAST_INSERT_ID() as id;");
        $pollID=mysql_fetch_assoc($res)['id'];
        $options= explode(',;,', $options);
        foreach ($options as $o){
            $o= mysql_real_escape_string($o);
            $query="INSERT INTO `Lgf`.`Answers` (`id`, `pollID`, `answer`) VALUES (NULL, '$pollID', '$o');";
            mysql_query($query);
        }
        return getPoll($pollID);
    }
    
    function vote($pollID,$userID,$decicion){
        $query="SELECT * FROM `Votes` WHERE `pollID`=$pollID AND `userID`=$userID";
        $res=mysql_query($query);
        if(mysql_num_rows($res)!=0){
            $id=mysql_fetch_assoc($res)['id'];
            $query="UPDATE `Lgf`.`Votes` SET `answerID` = '$decicion' WHERE `Votes`.`id` = $id;";
            if (mysql_query($query)==1){
                return getPoll($pollID);
            }
            else{
                return array("errorcode"=>"unknownError");
            }
        }
        else{
            $query="INSERT INTO `Lgf`.`Votes` (`id`, `userID`, `pollID`, `answerID`) VALUES (NULL, '$userID', '$pollID', '$decicion');";
            if (mysql_query($query)==1){
                return getPoll($pollID);
            }
            else{
                return array("errorcode"=>"unknownError");
            }
        }
    }
    ###################
    ##New Testing Area#
    ###################
    
    
    
    
    
    
    
    switch ($_POST['action']){
        case "login"://ok
            $jsonObj=doLogin($_POST['mail'], $_POST['pass']);
            break;
        case "resetPW"://ok
            $jsonObj=resetPW($_POST['mail']);
            break;
        case "register"://ok
            $jsonObj=register($_POST['mail'], $_POST['name'], $_POST['pass']);
            break;
        case "getUser"://ok
            $jsonObj=getUserByID($_POST['userID']);
            break;
        case "getUserInteressen"://ok
            $jsonObj=getUserIntrests($_POST['userID']);
            break;
        case "getInteressen"://ok
            $jsonObj=getStudyFields();
            break;
        case "getInteresseID"://ok
            $jsonObj=getStudyFieldsID($_POST['interestID']);
            break;
        case "getVeranstaltungID"://ok
            $jsonObj=getVeranstaltungByID($_POST['veranstaltungID']);
            break;
        case "getVeranstaltungenUmkreis"://ok
            $jsonObj=getVeranstaltungenUmkreis($_POST['gpsX'], $_POST['gpsY'], $_POST['radius']);
            break;
        case "setUserInteressen"://ok
            $jsonObj=setUserIntrests($_POST['userID'], $_POST['interests']);
            break;
        case "abboniereVeranstaltung"://ok
            $jsonObj=doAbo($_POST['userID'], $_POST['veranstaltungID']);
            break;
        case "getAbbonierteVeranstaltung"://ok
            $jsonObj=getAbos($_POST['userID']);
            break;
        case "createEvent"://ok
            $jsonObj=createEvent($_POST['name'], $_POST['beschreibung'], $_POST['fachgebiet'], $_POST['building'], $_POST['date'], $_POST['expires'], $_POST['gpsX'], $_POST['gpsY']);
            break;
        case "getBuildingsClose"://ok
            $jsonObj=getBuildingsClose($_POST['gpsX'], $_POST['gpsY'], $_POST['radius']);
            break;
        case "changePass":
            $jsonObj=changePw($_POST['userID'], $_POST['oldPW'], $_POST['newPW']);
            break;
        case "changeName":
            $jsonObj=changeName($_POST['userID'], $_POST['newName']);
            break;
        case "uploadProfilePicture":		
            $jsonObj=uploadProfilePicture($_POST['userID'], $_FILES);
            break;
        case "comment":		
            $jsonObj=doComment($_POST['userID'], $_POST['eventID'], $_POST['comment'], $_POST['pollID']);
            break;
            
        case "getEventFiles":
            $jsonObj=getEventFiles($_POST['eventID']);
            break;
            
        case "uploadEventFile":
            $jsonObj=uploadFileToEvent($_POST['eventID'], $_FILES);
            break;
            
        case "checkinToEvent":
            $jsonObj=checkinToEvent($_POST['userID'], $_POST['eventID']);
            break;
            
        case "getUsersCheckedIn":
            $jsonObj=getCheckedInUsers($_POST['eventID']);
            break;
            
        case "getComments":
            $jsonObj=getEventComments($_POST['eventID']);
            break;
            
        case "contactUser":
            $jsonObj=contactUser($_POST['userID'], $_POST['recipientID'], $_POST['content']);
            break;
            
        case "getPoll":
            $jsonObj=getPoll($_POST['pollID']);
            break;
            
        case "createPoll":
            $jsonObj=createPoll($_POST['question'], $_POST['options']);
            break;

        case "vote":
            $jsonObj=vote($_POST['pollID'], $_POST['userID'], $_POST['decicion']);
            break;


        default:
            $jsonObj=array("errorcode"=>"noActionSelected");
            print_r($_POST);
            break;	
    }
    echo json_encode($jsonObj);
    
    
    ?>
